package com.dhy.dao;

import com.dhy.dbCore.util.DbUtil;
import com.dhy.dbCore.JdbcTemplateFacade;
import com.dhy.domain.Stu;
import com.dhy.dto.StuCourseDto;

import java.util.List;

/**
 * @author 大忽悠
 * @create 2022/6/6 20:14
 */
public class StuDao {

    private JdbcTemplateFacade jt= DbUtil.getJT();

    public List<StuCourseDto> queryStuBySno(String sno) {
        List<StuCourseDto> stuCourseDtos = jt.list("SELECT s.sno,s.name,c.name courseName,c.type,sc.score\n" +
                "FROM stu s\n" +
                "INNER JOIN courseSelection sc\n" +
                "ON s.id=sc.sid\n" +
                "INNER JOIN course c\n" +
                "ON c.id=sc.cid\n" +
                "WHERE s.sno='" + sno + "'", StuCourseDto.class);
        return stuCourseDtos;
    }

    public List list(){
        return jt.list("SELECT s.id,s.sno,s.name,s.credits,c.name className FROM stu s INNER JOIN class c WHERE s.classId=c.id",
                null);
    }

    // 删除学生信息
    public Boolean deleteStu(String sno) {
        String sql="delete from stu where sno = '"+ sno +"';";
        return jt.delete(sql);
    }

    public Boolean insert(Stu stu) {
        String sql="INSERT INTO stu VALUES(NULL,'"+stu.getSno()+"','"+stu.getName()+"',"+stu.getCredits()+","+stu.getClassId()+")";
        return jt.insert(sql);
    }
}
